﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.CodeDom.Compiler;
using PDOGenerator.MapTypes;
using PDOGenerator.DbToMoreGen;

namespace PDOGenerator.Generators
{
    /// <summary>
    /// Stored Procedure generator.
    /// </summary>
    public class ToStoredProcedure : ClassGenerator
    {
        private Project _project;
        private StringWriter _otw;
        private string _className;

        protected delegate string WriteCommaLine(ColumnMap col);

        internal ToStoredProcedure(Project p)
        {
            _project = p;
        }

        public override string Generate(TableMap tbl)
        {
            _otw = new StringWriter();

            _className = FromDb.SafeClassName(tbl.TableName);

            GenerateDropProcs(tbl, tbl.IsUpdateable);
            GenerateSelectProc(tbl);

            if (tbl.IsUpdateable)
            {
                GenerateInsertProc(tbl);
                GenerateUpdateProc(tbl);
                GenerateDeleteProc(tbl);
            }

            return _otw.ToString();
        }


        private static List<string> _precisionDataType =
            new List<string>() { "char", "varchar", "binary", "varbinary", "nchar", "nvarchar" };

        public string SQLTypeToString(ColumnMap col)
        {
            string sqlType = "**SQL TYPE ERROR**";
            if (_precisionDataType.Contains(col.DBType))
            {
                sqlType = col.DBType.ToUpper() + "(" + col.ColumnPrecisionString + ")";
            }
            else
            {
                sqlType = col.DBType.ToUpper();
            }

            return sqlType;
        }

        /// <summary>
        /// Generate all drop procedures before the rest
        /// </summary>
        /// <param name="tblName"></param>
        protected void GenerateDropProcs(TableMap tbl, bool isUpdateable)
        {
            _otw.Write(Templates.TEMP_DROP_PROC, tbl.SchemaName, _project.StoredProcPrefix + _className + "Select");

            if (isUpdateable)
            {
                _otw.Write(Templates.TEMP_DROP_PROC, tbl.SchemaName, _project.StoredProcPrefix + _className + "Insert");
                _otw.Write(Templates.TEMP_DROP_PROC, tbl.SchemaName, _project.StoredProcPrefix + _className + "Update");
                _otw.Write(Templates.TEMP_DROP_PROC, tbl.SchemaName, _project.StoredProcPrefix + _className + "Delete");
                _otw.Write(Templates.TEMP_DROP_PROC, tbl.SchemaName, _project.StoredProcPrefix + _className + "Sync");
            }
        }

        protected void WriteProcLead(TableMap tbl)
        {
            Version ver = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version;
            _otw.Write(Templates.SQL_PROC_LEAD, ver.Major, ver.Minor);


            if (tbl.HasComputedKey)
            {
                _otw.WriteLine(@"
-- Required for Computed columns
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
GO"
                    );
            }

        }

        protected void WriteProcTail()
        {
            _otw.Write(Templates.SQL_PROC_TAIL);
        }

        protected void WriteWhereClause(TableMap tbl, string tabs)
        {
            ColumnMap col;

            _otw.WriteLine(tabs + "WHERE ");
            col = tbl.PrimaryKey();

            _otw.WriteLine("{0}[{1}] = @{2}", tabs, col.ColumnName, col.ColumnName);
        }

        protected string WriteInsertLineCols(ColumnMap col)
        {
            return "[" + col.ColumnName + "]";
        }

        protected string WriteInsertLineVals(ColumnMap col)
        {
            return "@" + col.SafeColumnName;
        }


        protected void WriteInsertList(List<ColumnMap> lst, WriteCommaLine fnLine, bool bPrefix)
        {
            ColumnMap col;
            for (int idx = 0; idx < lst.Count; idx++)
            {
                col = lst[idx];
                if (!col.Identity)
                {
                    if (bPrefix && idx == 0)
                    {
                        _otw.Write(", ");
                    }

                    _otw.Write(fnLine(col));

                    if (idx != lst.Count - 1)
                    {
                        _otw.Write(", ");
                    }
                }
            }
        }


        protected void GenerateSelectProc(TableMap tbl)
        {
            ColumnMap col;

            WriteProcLead(tbl);
            _otw.WriteLine("CREATE PROCEDURE [{0}].{1}{2}Select", tbl.SchemaName, _project.StoredProcPrefix, _className);

            if (tbl.IsView)
            {
                _otw.WriteLine("AS\r\n");

                string columnSelect = ToPDOClass.GetSelectColumnList(tbl, false, true, true, true);
                _otw.WriteLine("\tSELECT\t{0}\r\n\tFROM [{1}]", columnSelect, tbl.TableName);
                _otw.WriteLine("GO\r\n");
            }
            else
            {
                _otw.WriteLine("(");
                col = tbl.PrimaryKey();
                _otw.Write("@{0}\t{1} = NULL", col.ColumnName, SQLTypeToString(col));

                _otw.WriteLine("");
                _otw.WriteLine(")");

                _otw.WriteLine("AS\r\n");

                col = tbl.PrimaryKey();

                string columnSelect = ToPDOClass.GetSelectColumnList(tbl, true, true, true, true);
                _otw.WriteLine("IF @{0} IS NULL BEGIN", col.ColumnName);
                _otw.WriteLine("\tSELECT\t{0}\r\n\tFROM [{1}]", columnSelect, tbl.TableName);
                _otw.WriteLine("END ELSE BEGIN");
                _otw.WriteLine("\tSELECT\t{0}\r\n\tFROM [{1}]", columnSelect, tbl.TableName);
                WriteWhereClause(tbl, "\t");
                _otw.WriteLine("END\r\nGO\r\n");
            }





            WriteProcTail();
        }


        private void AppendColumnParameter(StringBuilder sb, ColumnMap col)
        {
            sb.Append("@" + col.SafeColumnName + "\t" + SQLTypeToString(col) +
                    (col.ColumnScale > 0 ? "(" + col.ColumnPrecisionString + ", " + col.ColumnScale + ")" : "") +
                    (col.Nullable ? " = null" : ""));
            sb.Append(",\r\n");
        }


        protected void GenerateInsertProc(TableMap tbl)
        {
            WriteCommaLine wclc = new WriteCommaLine(WriteInsertLineCols);
            WriteCommaLine wclv = new WriteCommaLine(WriteInsertLineVals);
            WriteProcLead(tbl);


            _otw.WriteLine("CREATE PROCEDURE [{0}].{1}{2}Insert", tbl.SchemaName, _project.StoredProcPrefix, _className);
            _otw.WriteLine("(");

            ColumnMap pkeyColumn = tbl.PrimaryKey();
            StringBuilder sb = new StringBuilder();

            if (!pkeyColumn.Identity)
            {
                AppendColumnParameter(sb, pkeyColumn);
            }

            foreach (ColumnMap col in tbl.Columns.Values)
            {
                if (!col.IsPK && !col.IsCollection && !col.Calculated)

                    AppendColumnParameter(sb, col);
            }
            _otw.Write(sb.ToString(0, sb.Length - 3)); // remove final ",\r\n"


            _otw.WriteLine(")");
            _otw.WriteLine("AS");


            bool pkeyIsGuid = (pkeyColumn.DataType == "Guid");
            //if (pkeyIsGuid) -- set pk Guid value in code instead of this
            //{
            //    pkeyIsGuid = true;
            //    otw.WriteLine("");
            //    otw.WriteLine("DECLARE @pk{0} UNIQUEIDENTIFIER", pkeyColumn.ColumnName);
            //    otw.WriteLine("SET @pk{0} = newid()", pkeyColumn.ColumnName);
            //    otw.WriteLine("");
            //}

            _otw.WriteLine("INSERT [{0}]", tbl.TableName);
            _otw.Write("\t(");
            if (pkeyIsGuid)
            {
                _otw.Write(string.Format("[{0}], ", pkeyColumn.ColumnName));
            }
            WriteInsertList(tbl.NativeNonKeyColumnList, wclc, false);


            _otw.WriteLine(")");


            _otw.WriteLine("VALUES");
            _otw.Write("(");
            if (pkeyIsGuid)
            {
                _otw.Write(string.Format("@{0}, ", pkeyColumn.SafeColumnName));
            }
            WriteInsertList(tbl.NativeNonKeyColumnList, wclv, false);
            _otw.WriteLine(")");

            if (pkeyIsGuid)
            {
                _otw.WriteLine(string.Format("\r\nSELECT @{0}", pkeyColumn.ColumnName));
            }
            else
            {
                _otw.WriteLine("\r\nSELECT SCOPE_IDENTITY()");
            }

            _otw.WriteLine("GO\r\n");
            WriteProcTail();
        }

        protected void GenerateUpdateProc(TableMap tbl)
        {
            ColumnMap col;

            WriteProcLead(tbl);

            _otw.WriteLine("CREATE PROCEDURE [{0}].{1}{2}Update", tbl.SchemaName, _project.StoredProcPrefix, _className);
            _otw.WriteLine("(");

            col = tbl.PrimaryKey();
            _otw.Write("@" + col.SafeColumnName + "\t" + SQLTypeToString(col) +
                (col.ColumnScale > 0 ? "(" + col.ColumnPrecisionString + ", " + col.ColumnScale + ")" : ""));

            if (tbl.NativeNonKeyColumnList.Count > 0)
            {
                _otw.WriteLine(", ");
            }
            else
            {
                _otw.WriteLine("");
            }

            for (int idx = 0; idx < tbl.NativeNonKeyColumnList.Count; idx++)
            {
                col = tbl.NativeNonKeyColumnList[idx];
                _otw.WriteLine("@" + col.SafeColumnName + "\t" + SQLTypeToString(col) +
                     (col.ColumnScale > 0 ? "(" + col.ColumnPrecisionString + ", " + col.ColumnScale + ")" : "") +
                     (col.Nullable ? " = null" : "") +
                     (idx == tbl.NativeNonKeyColumnList.Count - 1 ? "" : ", "));
            }

            _otw.WriteLine(")");
            _otw.WriteLine("AS");
            _otw.WriteLine("UPDATE [{0}]", tbl.TableName);

            _otw.Write("SET");

            for (int idx = 0; idx < tbl.NativeNonKeyColumnList.Count; idx++)
            {
                col = tbl.NativeNonKeyColumnList[idx];
                _otw.Write("\t[{0}] = @{1}", col.ColumnName, col.SafeColumnName);
                if (idx != tbl.NativeNonKeyColumnList.Count - 1)
                {
                    _otw.WriteLine(", ");
                }
            }
            // NOTE: No need to emit UPDATE statements for primary keys - they are always part of the WHERE

            _otw.WriteLine("");
            WriteWhereClause(tbl, string.Empty);

            _otw.WriteLine("GO\r\n");
            WriteProcTail();
        }


        protected void GenerateDeleteProc(TableMap tbl)
        {
            ColumnMap col;

            WriteProcLead(tbl);
            _otw.WriteLine("CREATE PROCEDURE [{0}].{1}{2}Delete", tbl.SchemaName, _project.StoredProcPrefix, _className);
            _otw.WriteLine("(");

            col = tbl.PrimaryKey();
            _otw.WriteLine("@" + col.ColumnName + "\t" + SQLTypeToString(col));
            _otw.WriteLine(")");
            _otw.WriteLine("AS");
            _otw.WriteLine("DELETE FROM [{0}]", tbl.TableName);

            WriteWhereClause(tbl, string.Empty);
            _otw.WriteLine("GO\r\n");

            WriteProcTail();

        }

        protected void GenerateSyncProc(TableMap tbl)
        {
            WriteCommaLine wclc = new WriteCommaLine(WriteInsertLineCols);
            WriteCommaLine wclv = new WriteCommaLine(WriteInsertLineVals);
            WriteProcLead(tbl);

            ColumnMap pkeyColumn = tbl.PrimaryKey();
            _otw.WriteLine("CREATE PROCEDURE [{0}].{1}{2}Sync", tbl.SchemaName, _project.StoredProcPrefix, _className);
            _otw.WriteLine("(");

            _otw.Write("@" + pkeyColumn.SafeColumnName + "\t" + SQLTypeToString(pkeyColumn) +
                    (pkeyColumn.ColumnScale > 0 ? "(" + pkeyColumn.ColumnPrecisionString + ", " + pkeyColumn.ColumnScale + ")" : ""));

            if (tbl.NativeNonKeyColumnList.Count > 0)
            {
                _otw.WriteLine(", ");
            }
            else
            {
                _otw.WriteLine("");
            }

            ColumnMap col;
            for (int idx = 0; idx < tbl.NativeNonKeyColumnList.Count; idx++)
            {
                col = tbl.NativeNonKeyColumnList[idx];
                _otw.WriteLine("@" + col.SafeColumnName + "\t" + SQLTypeToString(col) +
                     (col.ColumnScale > 0 ? "(" + col.ColumnPrecisionString + ", " + col.ColumnScale + ")" : "") +
                     (col.Nullable ? " = null" : "") +
                     (idx == tbl.NativeNonKeyColumnList.Count - 1 ? "" : ", "));
            }

            _otw.WriteLine(")");
            _otw.WriteLine("AS");
            _otw.WriteLine("IF NOT EXISTS (SELECT * FROM [{0}].[{1}] WHERE {2} = @{2})", tbl.SchemaName, tbl.TableName, pkeyColumn.ColumnName);
            _otw.WriteLine("BEGIN");


            _otw.WriteLine("\tINSERT [{0}]", tbl.TableName);
            _otw.Write("\t(");

            // Primary key column
            _otw.Write(string.Format("[{0}], ", pkeyColumn.ColumnName));

            WriteInsertList(tbl.NativeNonKeyColumnList, wclc, false);
            _otw.WriteLine(")");

            _otw.WriteLine("\tVALUES");
            _otw.Write("\t(");

            // Primary key column value
            _otw.Write(string.Format("@{0}, ", pkeyColumn.ColumnName));

            WriteInsertList(tbl.NativeNonKeyColumnList, wclv, false);
            _otw.WriteLine(")");
            _otw.WriteLine("END");
            _otw.WriteLine("ELSE");
            _otw.WriteLine("BEGIN");

            _otw.WriteLine("\tUPDATE [{0}]", tbl.TableName);
            _otw.Write("\tSET");
            for (int idx = 0; idx < tbl.NativeNonKeyColumnList.Count; idx++)
            {
                col = tbl.NativeNonKeyColumnList[idx];
                _otw.Write("\t\t[{0}] = @{1}", col.ColumnName, col.SafeColumnName);
                if (idx != tbl.NativeNonKeyColumnList.Count - 1)
                {
                    _otw.WriteLine(", ");
                }
            }

            _otw.WriteLine("");
            WriteWhereClause(tbl, "\t");

            _otw.WriteLine("END");
            _otw.WriteLine("GO\r\n");
            WriteProcTail();
        }


        //protected void GenerateAuditProc(TableMap tbl)
        //{
        //    WriteCommaLine wclc = new WriteCommaLine(WriteInsertLineCols);
        //    WriteCommaLine wclv = new WriteCommaLine(WriteInsertLineVals);
        //    WriteProcLead(tbl);


        //    _otw.WriteLine("CREATE PROCEDURE [{0}].{1}{2}AuditInsert", tbl.SchemaName, _project.StoredProcPrefix, _className);
        //    _otw.WriteLine("(");

        //    ColumnMap pkeyColumn = tbl.PrimaryKey();
        //    StringBuilder sb = new StringBuilder();

        //    if (!pkeyColumn.Identity)
        //    {
        //        AppendColumnParameter(sb, pkeyColumn);
        //    }

        //    foreach (ColumnMap col in tbl.Columns.Values)
        //    {
        //        if (!col.IsPK && !col.IsCollection && !col.Calculated)

        //            AppendColumnParameter(sb, col);
        //    }
        //    _otw.Write(sb.ToString(0, sb.Length - 3)); // remove final ",\r\n"


        //    _otw.WriteLine(")");
        //    _otw.WriteLine("AS");


        //    bool pkeyIsGuid = (pkeyColumn.DataType == "Guid");

        //    _otw.WriteLine("INSERT [{0}]", tbl.TableName);
        //    _otw.Write("\t(");
        //    if (pkeyIsGuid)
        //    {
        //        _otw.Write(string.Format("[{0}], ", pkeyColumn.ColumnName));
        //    }
        //    WriteInsertList(tbl.NativeNonKeyColumnList, wclc, false);


        //    _otw.WriteLine(")");


        //    _otw.WriteLine("VALUES");
        //    _otw.Write("(");
        //    if (pkeyIsGuid)
        //    {
        //        _otw.Write(string.Format("@{0}, ", pkeyColumn.SafeColumnName));
        //    }
        //    WriteInsertList(tbl.NativeNonKeyColumnList, wclv, false);
        //    _otw.WriteLine(")");

        //    if (pkeyIsGuid)
        //    {
        //        _otw.WriteLine(string.Format("\r\nSELECT @{0}", pkeyColumn.ColumnName));
        //    }
        //    else
        //    {
        //        _otw.WriteLine("\r\nSELECT SCOPE_IDENTITY()");
        //    }

        //    _otw.WriteLine("GO\r\n");
        //    WriteProcTail();

        //}


    }

}
